package com.geline.easyexcel.utils;

import cn.hutool.core.util.StrUtil;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.converters.Converter;
import com.alibaba.excel.converters.date.DateStringConverter;
import com.alibaba.excel.converters.localdate.LocalDateStringConverter;
import com.alibaba.excel.write.builder.ExcelWriterBuilder;
import com.alibaba.excel.write.handler.WriteHandler;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy;
import com.geline.easyexcel.domain.DropDownItem;
import com.geline.easyexcel.handler.CellFormulaWriteHandler;
import com.geline.easyexcel.handler.ExcelDropDownHandler;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.collections4.MapUtils;
import org.apache.commons.compress.utils.Lists;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.util.*;

/**
 * Excel工具类
 * @author cheng-qiang
 * @date 2022年08月16日10:13
 */
@Slf4j
public class EasyExcelUtils {

    /**默认sheet名称**/
    private static final String DEFAULT_SHEET_NAME = "sheet1";

    /**
     * 设置表格样式（头是头的样式、内容是内容的样式）
     * @author cheng-qiang
     * @date 2022/8/16 10:23
     * @param fillForegroundColorHead 头部背景色 IndexedColors.PINK.getIndex()
     * @param fontHeightInPointsHead 头部字体大小
     * @param fillForegroundColorContent 内容背景色 IndexedColors.LEMON_CHIFFON.getIndex()
     * @param fontHeightInPointsContent 内容字体大小
     * @param borderStyle 边框样式 BorderStyle.DASHED
     * @return com.alibaba.excel.write.style.HorizontalCellStyleStrategy
     */
    public static HorizontalCellStyleStrategy createTableStyle(Short fillForegroundColorHead, int fontHeightInPointsHead,
                                                               Short fillForegroundColorContent, int fontHeightInPointsContent, BorderStyle borderStyle) {
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        headWriteCellStyle.setFillForegroundColor(fillForegroundColorHead);
        WriteFont headWriteFont = new WriteFont();
        headWriteFont.setFontHeightInPoints((short)fontHeightInPointsHead);
        headWriteCellStyle.setWriteFont(headWriteFont);
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        contentWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
        contentWriteCellStyle.setFillForegroundColor(fillForegroundColorContent);
        WriteFont contentWriteFont = new WriteFont();
        contentWriteFont.setFontHeightInPoints((short)fontHeightInPointsContent);
        contentWriteCellStyle.setWriteFont(contentWriteFont);
        contentWriteCellStyle.setBorderBottom(borderStyle);
        contentWriteCellStyle.setBorderLeft(borderStyle);
        contentWriteCellStyle.setBorderRight(borderStyle);
        contentWriteCellStyle.setBorderTop(borderStyle);
        return new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
    }

    /**
     * 导出单个sheet表格
     * @author cheng-qiang
     * @date 2022/8/16 10:32
     * @param response HttpServletResponse
     * @param list 数据列表
     * @param fileName 文件名称
     * @param sheetName sheet名称
     * @param clazz 数据类型
     * @param fillForegroundColorHead 头部背景色 IndexedColors.PINK.getIndex()
     * @param fontHeightInPointsHead 头部字体大小
     * @param fillForegroundColorContent 内容背景色 IndexedColors.LEMON_CHIFFON.getIndex()
     * @param fontHeightInPointsContent 内容字体大小
     * @param borderStyle 边框样式 BorderStyle.DASHED
     */
    public static void writeSingleExcel(HttpServletResponse response,
                                        List<?> list, String fileName, String sheetName, Class<?> clazz, Short fillForegroundColorHead,
                                        int fontHeightInPointsHead, Short fillForegroundColorContent, int fontHeightInPointsContent, BorderStyle borderStyle)  {
        try {
            response.setCharacterEncoding("utf8");
            response.setContentType("application/vnd.ms-excel;charset=utf-8");
            response.setHeader("Content-Disposition", "attachment; filename="+ URLEncoder.encode(fileName + ".xlsx", "UTF-8"));
            response.setHeader("Cache-Control", "no-store");
            response.addHeader("Cache-Control", "max-age=0");
            EasyExcel.write(response.getOutputStream(), clazz)
                    .sheet(sheetName).registerWriteHandler(createTableStyle(
                            fillForegroundColorHead,
                            fontHeightInPointsHead,
                            fillForegroundColorContent,
                            fontHeightInPointsContent,
                            borderStyle
                    ))
                    .doWrite(list);
        }catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 导出多个sheet表格
     * @author cheng-qiang
     * @date 2022/8/16 10:37
     * @param response HttpServletResponse
     * @param listMap key 是 sheet名称,value 是 数据列表
     * @param fileName 文件名称
     * @param classMap key是listMap中的key,value是对应的类型
     * @param fillForegroundColorHead 头部背景色 IndexedColors.PINK.getIndex()
     * @param fontHeightInPointsHead 头部字体大小
     * @param fillForegroundColorContent 内容背景色 IndexedColors.LEMON_CHIFFON.getIndex()
     * @param fontHeightInPointsContent 内容字体大小
     * @param borderStyle 边框样式 BorderStyle.DASHED
     */
    public static void writeMultiExcel(HttpServletResponse response, Map<String,List<?>> listMap, String fileName, Map<String,Class<?>> classMap, Short fillForegroundColorHead,
                                       int fontHeightInPointsHead, Short fillForegroundColorContent, int fontHeightInPointsContent, BorderStyle borderStyle, String password){
        try {
            response.setCharacterEncoding("utf8");
            response.setContentType("application/vnd.ms-excel;charset=utf-8");
            response.setHeader("Content-Disposition", "attachment; filename="+ URLEncoder.encode(fileName + ".xlsx", "UTF-8"));
            response.setHeader("Cache-Control", "no-store");
            response.addHeader("Cache-Control", "max-age=0");
            ExcelWriterBuilder builder = EasyExcel.write(response.getOutputStream());
            if(StrUtil.isNotBlank(password)){
                builder.password(password);
            }
            ExcelWriter writeMultiExcel = builder.build();
            Set<String> keySet = listMap.keySet();
            int count = 0;
            for (String key : keySet) {
                List<?> list = listMap.get(key);
                WriteSheet writeSheet = EasyExcel.writerSheet(count, key).head(classMap.get(key)).registerWriteHandler(createTableStyle(
                        fillForegroundColorHead,
                        fontHeightInPointsHead,
                        fillForegroundColorContent,
                        fontHeightInPointsContent,
                        borderStyle
                )).build();
                writeMultiExcel.write(list,writeSheet);
                count++;
            }
            writeMultiExcel.finish();
        }catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 导出Excel文件
     * @author cheng-qiang
     * @date 2022/8/16 15:07
     * @param headColumnMap 有序列头部
     * @param dataList 数据体
     * @param response HttpServletResponse
     * @param fileName 文件名称
     */
    public static void exportExcelFile(Map<String, String> headColumnMap, List<Map<String, Object>> dataList, HttpServletResponse response,String fileName){
        try {
            List<List<String>> excelHead = new ArrayList<>();
            if(MapUtils.isNotEmpty(headColumnMap)){
                headColumnMap.forEach((key, value) -> excelHead.add(Lists.newArrayList(Arrays.stream(value.split(",")).iterator())));
            }
            List<List<Object>> excelRows = new ArrayList<>();
            if(MapUtils.isNotEmpty(headColumnMap) && CollectionUtils.isNotEmpty(dataList)){
                for (Map<String, Object> dataMap : dataList) {
                    List<Object> rows = new ArrayList<>();
                    headColumnMap.forEach((key, value) -> {
                        if (dataMap.containsKey(key)) {
                            Object data = dataMap.get(key);
                            rows.add(data);
                        }
                    });
                    excelRows.add(rows);
                }
            }
            createExcelFile(excelHead, excelRows, response, fileName);
        }catch (Exception ignored){
        }
    }

    /**
     * 创建Excel文件
     * @author cheng-qiang
     * @date 2022/8/16 15:07
     * @param excelHead 表头标题
     * @param excelRows 表行数据
     * @param response HttpServletResponse
     * @param fileName 文件名称
     */
    public static void createExcelFile(List<List<String>> excelHead, List<List<Object>> excelRows, HttpServletResponse response, String fileName){
        try {
            if(CollectionUtils.isNotEmpty(excelHead)){
                response.setCharacterEncoding("utf8");
                response.setContentType("application/vnd.ms-excel;charset=utf-8");
                response.setHeader("Content-Disposition", "attachment; filename="+ URLEncoder.encode(fileName + ".xlsx", "UTF-8"));
                response.setHeader("Cache-Control", "no-store");
                response.addHeader("Cache-Control", "max-age=0");
                EasyExcel.write(response.getOutputStream()).registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
                        .head(excelHead)
                        .sheet(DEFAULT_SHEET_NAME)
                        .doWrite(excelRows);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 导出Excel数据
     * @param response
     * @param fileName 文件名
     * @param dataList 当前页数据列表
     * @param headClass
     * @param variableMap 替换变量
     * @param ins 生成模板
     */
    public static void writeSelectedSheet(HttpServletResponse response, String fileName, List dataList,
                                          Class headClass, Map<String, String> variableMap, InputStream ins, String password) {
        ArrayList<Converter> converterList = new ArrayList<>();
        // Date --> String
        converterList.add(new DateStringConverter());
        // LocalDate --> String
        converterList.add(new LocalDateStringConverter());
        //封装response头
        try {
            initResponse(response, fileName);
            //构建 ExcelWriter
            ExcelWriterBuilder builder = initExcelWriter(response, null, converterList);
            if(StrUtil.isNotBlank(password)){
                builder.password(password);
            }
            ExcelWriter excelWriter = builder.withTemplate(ins).build();
            //写入excel
            WriteSheet writeSheet = EasyExcel.writerSheet(0, "Sheet1")
                    .registerWriteHandler(new CellFormulaWriteHandler(headClass))
                    .registerWriteHandler(new ExcelDropDownHandler(headClass))
                    .build();

            if(dataList!=null){
                excelWriter.fill(dataList, writeSheet);
                log.debug("fill dataList = {}", dataList.size());
            }
            if(variableMap!=null){
                excelWriter.fill(variableMap, writeSheet);
                log.debug("fill variableMap = {}", variableMap);
            }
            excelWriter.finish();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 导出单个sheet动态数据选项模板
     * @author cheng-qiang
     * @date 2022/11/16 15:49
     * @param head 表格头信息
     * @param sheetNo sheet下标
     * @param sheetName sheen名称
     * @param fileName 文件名称
     * @param response HttpServletResponse
     */
    public static void writeSelectedSheet(Class head, Integer sheetNo, String sheetName, String fileName, HttpServletResponse response) throws IOException {
        response.setContentType("application/vnd.ms-excel");
        response.setHeader("Content-Disposition", "attachment; filename="+ URLEncoder.encode(fileName + ".xlsx", "UTF-8"));
        response.setHeader("Cache-Control", "no-cache");
        response.setHeader("Pragma", "no-cache");
        response.setDateHeader("Expires", -1);
        response.setCharacterEncoding("UTF-8");
        ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).build();
        WriteSheet writeSheet = EasyExcel.writerSheet(sheetNo, sheetName)
                .head(head)
                .registerWriteHandler(new CellFormulaWriteHandler(head))
                .registerWriteHandler(new ExcelDropDownHandler(head))
                .build();
        excelWriter.write(new ArrayList<>(), writeSheet);
        excelWriter.finish();
    }

    /**
     * 导出多个sheet动态数据选项模板
     * @author cheng-qiang
     * @date 2022/11/16 15:49
     * @param classMap 头信息
     * @param sheetName sheen名称
     * @param fileName 文件名称
     * @param response HttpServletResponse
     */
    public static void writeSelectedSheet(Map<String,Class<?>> classMap, List<String> sheetName, String fileName, HttpServletResponse response) throws IOException {
        response.setContentType("application/vnd.ms-excel");
        response.setHeader("Content-Disposition", "attachment; filename="+ URLEncoder.encode(fileName + ".xlsx", "UTF-8"));
        response.setHeader("Cache-Control", "no-cache");
        response.setHeader("Pragma", "no-cache");
        response.setDateHeader("Expires", -1);
        response.setCharacterEncoding("UTF-8");
        ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).build();
        int count = 0;
        for (String key : sheetName) {
            Class<?> headClass = classMap.get(key);
            WriteSheet writeSheet = EasyExcel.writerSheet(count, key)
                    .head(headClass)
                    .registerWriteHandler(new CellFormulaWriteHandler(headClass))
                    .registerWriteHandler(new ExcelDropDownHandler(headClass))
                    .build();
            excelWriter.write(new ArrayList<>(), writeSheet);
            count++;
        }
        excelWriter.finish();
    }

    /**
     * 静态下拉框-纯文本
     * @author cheng-qiang
     * @date 2022/11/12 17:19
     * @param sheet Sheet
     * @param helper DataValidationHelper
     * @param k Integer
     * @param v ExcelSelectedResolve
     */
    public static void selectStillness(Sheet sheet, DataValidationHelper helper, Integer k, DropDownItem v) {
        CellRangeAddressList rangeList = new CellRangeAddressList(v.getDropDown().firstRow(), v.getDropDown().lastRow(), k, k);
        DataValidationConstraint constraint;
        Collection<String> itemList = v.getItemList();
        constraint = helper.createExplicitListConstraint(itemList.toArray(new String[]{}));
        DataValidation validation = helper.createValidation(constraint, rangeList);
        validation.setErrorStyle(DataValidation.ErrorStyle.STOP);
        validation.setShowErrorBox(true);
        validation.setSuppressDropDownArrow(true);
        validation.createErrorBox("友情提示", "请输入下拉选项中的内容");
        sheet.addValidationData(validation);
    }

    /**
     * 导出初始化response
     * @param response
     * @throws UnsupportedEncodingException
     */
    public static void initResponse(HttpServletResponse response, String fileName) throws UnsupportedEncodingException {
        response.reset();
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        fileName = fileName+".xlsx";
        response.addHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
    }

    /**
     * 构建writer
     * @param response
     * @param writeHandlerList
     * @param converterList
     * @throws IOException
     */
    public static ExcelWriterBuilder initExcelWriter(HttpServletResponse response, List<WriteHandler> writeHandlerList, List<Converter> converterList) throws IOException {
        ExcelWriterBuilder write = EasyExcel.write(response.getOutputStream());
        writeHandlerList = Optional.ofNullable(writeHandlerList).orElse(new ArrayList<>());
        writeHandlerList.stream().forEach(writeHandler -> write.registerWriteHandler(writeHandler));
        //自适应列宽
        //write.registerWriteHandler(new LongestMatchColumnWidthStyleStrategy());
        //翻译类注册
        converterList = Optional.ofNullable(converterList).orElse(new ArrayList<>());
        converterList.stream().forEach(converter -> write.registerConverter(converter));
        return write;
    }
}
